package com.overtime.expense.export;

import com.exception.AppException;
import com.overtime.expense.bean.ExpenseFeeBean;
import com.overtime.expense.bean.ExportExpenseBean;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.util.ObjectUtils;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.List;


public class ExportExcelUtil {

    private static final String SHEET_NAME_1 = "项目费用明细单";
    private static final int SHEET_MERGE_SIZE_1 = 6;
    private static final String SHEET_NAME_2 = "加班人员明细单";
    private static final String SHEET_NAME_3 = "加班报销金额统计表";
    private static final String SHEET_NAME_4 = "加班人员信息表";

    public static final int PERCENT_WIDTH = 60;
    public static final int PERCENT_HEIGHT = 20;
    public static final float PXTOPT = 0.75f;


    public static void exportExcel(Map<String, List<ExportExpenseBean>> dateMaps, HttpServletResponse response, String departName, String projectName, String projectNumber, String cause, String userName) throws Exception {
        try {
            if (dateMaps.size() == 0) {
                throw new AppException("导出报销单数据为空！");
            }
            HSSFWorkbook workbookFeeDetail = getWorkbookFeeDetail(dateMaps, departName, projectName, projectNumber, cause, userName);
            String fileName = "加班统计报销单.xls";
            fileName = URLEncoder.encode(fileName,"UTF8");
            String headStr = "attachment; filename=\"" + fileName + "\"";
            response.setContentType("APPLICATION/OCTET-STREAM");
            response.setHeader("Content-Disposition", headStr);
            OutputStream out = response.getOutputStream();
            workbookFeeDetail.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获得项目费用明细单
    public static HSSFWorkbook getWorkbookFeeDetail(Map<String, List<ExportExpenseBean>> dateMaps, String departName, String projectName, String projectNumber, String cause, String userName) throws Exception {
        // 创建工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            //项目费用明细单
            HSSFSheet sheet = workbook.createSheet(SHEET_NAME_1);
            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);
            //获取列头样式对象
            HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);
            HSSFCellStyle style = getStyle(workbook);
            HSSFCellStyle jeStyle = getJEStyle(workbook);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (SHEET_MERGE_SIZE_1 - 1)));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(SHEET_NAME_1);
            //第2行
            HSSFRow row2 = sheet.createRow(1);
            HSSFCell cell21 = row2.createCell(0);
            cell21.setCellValue("部门");
            sheet.setDefaultColumnWidth(sheet.getDefaultColumnWidth() * 2);
            sheet.setDefaultColumnStyle(0, style);
            sheet.setColumnWidth(0, sheet.getColumnWidth(0) * 6);
            HSSFCell cell22 = row2.createCell(1);
            cell22.setCellValue(departName);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, (SHEET_MERGE_SIZE_1 - 1)));
            cell21.setCellStyle(style);
            //第3行
            HSSFRow row3 = sheet.createRow(2);
            HSSFCell cell31 = row3.createCell(0);
            cell31.setCellValue("项目名称");
            HSSFCell cell32 = row3.createCell(1);
            cell32.setCellValue(projectName);
            sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, (SHEET_MERGE_SIZE_1 - 1)));
            cell31.setCellStyle(style);
            //第4行
            HSSFRow row4 = sheet.createRow(3);
            HSSFCell cell41 = row4.createCell(0);
            cell41.setCellValue("项目编号");
            HSSFCell cell42 = row4.createCell(1);
            cell42.setCellValue(projectNumber);
            sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, (SHEET_MERGE_SIZE_1 - 1)));
            cell41.setCellStyle(style);
            //第5行
            HSSFRow row5 = sheet.createRow(4);
            HSSFCell cell51 = row5.createCell(0);
            cell51.setCellValue("加班事由");
            HSSFCell cell52 = row5.createCell(1);
            cell52.setCellValue(cause);
            sheet.addMergedRegion(new CellRangeAddress(4, 4, 1, (SHEET_MERGE_SIZE_1 - 1)));
            cell51.setCellStyle(style);
            String[] arrs = new String[]{"人员名单", "日期", "日期类别", "餐费", "交通费", "小计"};
            //标题行
            HSSFRow row6 = sheet.createRow(5);
            for (int i = 0; i < arrs.length; i++) {
                String arr = arrs[i];
                HSSFCell cell6 = row6.createCell(i);
                cell6.setCellValue(arr);
            }

            //创建加班人员明细表
            HSSFSheet sheet2 = workbook.createSheet(SHEET_NAME_2);
            // 产生表格标题行
            HSSFRow rowm2 = sheet2.createRow(0);
            HSSFCell cellTiltle2 = rowm2.createCell(0);
            //获取列头样式对象
            sheet2.setDefaultColumnWidth(sheet.getDefaultColumnWidth());
            //sheet2.setDefaultColumnStyle(0, style);
            int columnWidth = sheet2.getColumnWidth(0);
            sheet2.setColumnWidth(0, columnWidth);
            sheet2.setColumnWidth(1, columnWidth);
            sheet2.setColumnWidth(2, columnWidth);
            sheet2.setColumnWidth(3, columnWidth * 2);
            sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
            cellTiltle2.setCellStyle(columnTopStyle);
            cellTiltle2.setCellValue(SHEET_NAME_2);
            String[] arrs2 = new String[]{"加班时间", "加班地点", "加班事由", "加班人员"};
            //标题行
            HSSFRow rowS2 = sheet2.createRow(1);
            for (int i = 0; i < arrs2.length; i++) {
                String arr = arrs2[i];
                HSSFCell cellS2 = rowS2.createCell(i);
                cellS2.setCellValue(arr);
                cellS2.setCellStyle(style);
                //sheet2.setDefaultColumnStyle(i, style);
            }

            //创建加班报销金额统计表
            HSSFSheet sheet3 = workbook.createSheet(SHEET_NAME_3);
            // 产生表格标题行
            HSSFRow rowm3 = sheet3.createRow(0);
            HSSFCell cellTiltle3 = rowm3.createCell(0);
            cellTiltle3.setCellStyle(columnTopStyle);
            cellTiltle3.setCellValue(SHEET_NAME_3);
            //rowm3.setHeightInPoints(77 * PXTOPT);
            //斜线拆分单元格
            HSSFRow rowm3P = sheet3.createRow(1);
            HSSFCell cellP1 = rowm3P.createCell(0);
            //rowm3P.setHeightInPoints(50 * PXTOPT);
            cellP1.setCellStyle(getPStyle(workbook));
            //cellP1.setCellValue("\n                   人员\n\n日期           金额");
            cellP1.setCellValue("                     人员\r\n日期          金额");
            //int x1 = 61, y1 = 77;
            int x1 = 61, y1 = 30;
            int x2 = 164, y2 = 23;
            //int x2 = 164, y2 = 61;
            int[] xys = {x1, y1, x2, y2};
            //drawLine(sheet3, rowm3P, 1, 0, 164, 70, xys);
            drawLine(sheet3, rowm3P, 1, 0, 164, 30, xys);

            //创建加班人员信息表
            HSSFSheet sheet4 = workbook.createSheet(SHEET_NAME_4);
            // 产生表格标题行
            HSSFRow rowm4 = sheet4.createRow(0);
            HSSFCell cellTiltle4 = rowm4.createCell(0);
            //获取列头样式对象
            sheet4.setDefaultColumnWidth(sheet.getDefaultColumnWidth());
            int columnWidth4 = sheet4.getColumnWidth(0);
            sheet4.setColumnWidth(0, columnWidth4*2);
            sheet4.setColumnWidth(1, columnWidth4*2);
            sheet4.setColumnWidth(2, columnWidth4);
            sheet4.setColumnWidth(3, columnWidth4);
            sheet4.setColumnWidth(4, columnWidth4);
            sheet4.setColumnWidth(5, columnWidth4);
            sheet4.setColumnWidth(6, columnWidth4);
            sheet4.setColumnWidth(7, columnWidth4);
            sheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
            cellTiltle4.setCellStyle(columnTopStyle);
            cellTiltle4.setCellValue(SHEET_NAME_4);
            String[] arrs4 = new String[]{"项目编号", "加班事由", "加班地点", "加班日期","加班人员","餐费","交通费","人员部门"};
            //标题行
            HSSFRow rowS4 = sheet4.createRow(1);
            for (int i = 0; i < arrs4.length; i++) {
                String arr = arrs4[i];
                HSSFCell cellS4 = rowS4.createCell(i);
                cellS4.setCellValue(arr);
                cellS4.setCellStyle(style);
            }



            Set<Map.Entry<String, List<ExportExpenseBean>>> entries = dateMaps.entrySet();
            Iterator<Map.Entry<String, List<ExportExpenseBean>>> iterator = entries.iterator();
            int row = 6;
            int rowS = 2;
            int rowP = 2;
            int cp = 1;
            int rowSS4 = 2;
            Map<String, Integer> userMap = new HashMap<>();
            BigDecimal totalSum = new BigDecimal("0");
            while (iterator.hasNext()) {
                Map.Entry<String, List<ExportExpenseBean>> next = iterator.next();
                String key = next.getKey();
                List<ExportExpenseBean> value = next.getValue();
                StringBuffer sb = new StringBuffer();
                BigDecimal total = new BigDecimal("0");
                String dateType = "";
                String application_place = "";
                String cause_item = "";
                //** 加班报销金额统计表日期
                HSSFRow rowPP = sheet3.createRow(rowP);
                rowPP.setHeightInPoints(20);
                HSSFCell cellDate = rowPP.createCell(0);
                cellDate.setCellValue(key);
                cellDate.setCellStyle(style);
                for (int i = 0; i < value.size(); i++) {
                    ExportExpenseBean exportExpenseBean = value.get(i);
                    sb.append(exportExpenseBean.getUserName());
                    dateType = exportExpenseBean.getDate_Type();
                    if("1".equals(dateType)){
                        dateType = "工作日";
                    }else{
                        dateType = "周末";
                    }
                    application_place = exportExpenseBean.getApplication_place();
                    cause_item = exportExpenseBean.getCause();
                    if (i != value.size() && i != (value.size() - 1)) {
                        sb.append("、");
                    }
                    BigDecimal sum = new BigDecimal("0");
                    List<ExpenseFeeBean> expenseFeeList = exportExpenseBean.getExpenseFeeList();
                    for (int j = 0; j < expenseFeeList.size(); j++) {
                        ExpenseFeeBean expenseFeeBean = expenseFeeList.get(j);
                        if (expenseFeeBean.getFee() != null) {
                            sum = sum.add(expenseFeeBean.getFee());
                        }
                    }
                    total = total.add(sum);
                    //加班报销金额统计表金额
                    if (userMap.get(exportExpenseBean.getUser_id()) == null) {
                        HSSFCell cell3P = rowm3P.createCell(cp);
                        cell3P.setCellValue(exportExpenseBean.getUserName());
                        cell3P.setCellStyle(style);
                        userMap.put(exportExpenseBean.getUser_id(), cp);
                        cp++;
                    }
                    //所在列
                    int cnum = userMap.get(exportExpenseBean.getUser_id());
                    HSSFCell cellUser = rowPP.createCell(cnum);
                    cellUser.setCellValue(sum.toString());
                    cellUser.setCellStyle(jeStyle);
                }
                //保留两位小数
                total.setScale(2);
                //创建行
                HSSFRow rowData = sheet.createRow(row);
                //人员名单
                HSSFCell cell0 = rowData.createCell(0);
                cell0.setCellValue(sb.toString());
                //日期
                HSSFCell cell1 = rowData.createCell(1);
                cell1.setCellValue(key);
                //日期类别
                HSSFCell cell2 = rowData.createCell(2);
                cell2.setCellValue(dateType);
//                cell2.setCellStyle(setDateTybeStyle(workbook,dateType));
                //餐费
                HSSFCell cell3 = rowData.createCell(3);
                cell3.setCellValue("");
                //交通费
                HSSFCell cell4 = rowData.createCell(4);
                cell4.setCellValue("");
                //小计
                HSSFCell cell5 = rowData.createCell(5);
                cell5.setCellValue(total.toString());

                //** 加班人员明细单 开始
                HSSFRow rowSData = sheet2.createRow(rowS);
                //加班时间
                HSSFCell cellS0 = rowSData.createCell(0);
                cellS0.setCellValue(key);
                //加班地点
                HSSFCell cellS1 = rowSData.createCell(1);
                cellS1.setCellValue(application_place);
                //加班事由
                HSSFCell cellS2 = rowSData.createCell(2);
                cellS2.setCellValue(cause_item);
                //加班人员
                HSSFCell cellS3 = rowSData.createCell(3);
                cellS3.setCellValue(sb.toString());
                //** 加班人员明细单 结束

                //加班人员信息开始
                List<ExportExpenseBean> value1 = next.getValue();
                for (ExportExpenseBean exportExpenseBean:value1) {
                    String username4 = exportExpenseBean.getUserName();
                    String dateType4 = exportExpenseBean.getDate_Type();
                    String application_place4 = exportExpenseBean.getApplication_place();
                    String cause_item4 = exportExpenseBean.getCause();
                    //加班人员信息表开始
                    HSSFRow rowSData4 = sheet4.createRow(rowSS4);
                    //项目编号
                    HSSFCell cellS40 = rowSData4.createCell(0);
                    cellS40.setCellValue(projectNumber);
                    //加班事由
                    HSSFCell cellS41 = rowSData4.createCell(1);
                    cellS41.setCellValue(cause_item4);
                    //加班地点
                    HSSFCell cellS42 = rowSData4.createCell(2);
                    cellS42.setCellValue(application_place4);

                    //加班日期
                    HSSFCell cellS43 = rowSData4.createCell(3);
                    cellS43.setCellValue(key);

                    //加班人员
                    HSSFCell cellS44 = rowSData4.createCell(4);
                    cellS44.setCellValue(username4);

                    //餐费
                    HSSFCell cellS45 = rowSData4.createCell(5);
                    //交通费
                    HSSFCell cellS46 = rowSData4.createCell(6);
                    cellS45.setCellType(CellType.STRING);
                    cellS46.setCellType(CellType.STRING);
                    if("1".equals(dateType4)){
                        cellS45.setCellValue(20);
                        cellS46.setCellValue(0);
                    }else if("2".equals(dateType4)){
                        cellS45.setCellValue(50);
                        cellS46.setCellValue(50);
                    }
                    //人员部门
                    HSSFCell cellS47 = rowSData4.createCell(7);
                    cellS47.setCellValue(departName);
                    rowSS4++;
                }
                //加班人员信息表结束

                row++;
                totalSum = totalSum.add(total);
                rowS++;
                rowP++;
            }
            //合计
            HSSFRow rowH = sheet.createRow(row);
            HSSFCell cellH1 = rowH.createCell(0);
            cellH1.setCellValue("合计");
            cellH1.setCellStyle(style);
            HSSFCell cellH5 = rowH.createCell(5);
            totalSum.setScale(2);
            cellH5.setCellValue(totalSum.toString());
            row++;
            //制表人
            HSSFRow rowZ = sheet.createRow(row);
            HSSFCell cellZ1 = rowZ.createCell(0);
            cellZ1.setCellValue("制表人");
            HSSFCell cellZ2 = rowZ.createCell(1);
            cellZ2.setCellValue(userName);
            sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 5));
            HSSFCell cellZ3 = rowZ.createCell(3);
            cellZ3.setCellValue("审批人：");

            //** 加班人员明细单 领导签字
            HSSFRow rowSL = sheet2.createRow(rowS);
            HSSFCell cellSL = rowSL.createCell(2);
            HSSFCell cell2 = rowSL.createCell( 3);
            cellSL.setCellValue("领导签字: ");
            cell2.setCellValue("");
            cellSL.setCellStyle(style);
            cell2.setCellStyle(style);
            //加班报销金额统计表金额
            //合计
            HSSFRow rowHJ = sheet3.createRow(rowP);
            rowHJ.setHeightInPoints(20);
            HSSFCell cellHJ = rowHJ.createCell(0);
            cellHJ.setCellValue("合计");
            sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, cp));
            cellHJ.setCellStyle(style);
            //总计
            HSSFRow rowPP = sheet3.getRow(1);
            HSSFCell cellZJ = rowPP.createCell(cp);
            cellZJ.setCellValue("总计");
            cellZJ.setCellStyle(style);
            //计算
            for (int i = 2; i < rowP; i++) {
                HSSFRow row1 = sheet3.getRow(i);
                BigDecimal zj = new BigDecimal("0");
                for (int j = 1; j < cp; j++) {
                    HSSFCell cell = row1.getCell(j);
                    if(!ObjectUtils.isEmpty(cell)){
                        String value = cell.getStringCellValue();
                        if(value.matches("-?[0-9]+.?[0-9]+")){
                            zj = zj.add(new BigDecimal(value));
                        }
                    }
                }
                HSSFCell cell1 = row1.createCell(cp);
                cell1.setCellValue(zj.toString());
                cell1.setCellStyle(jeStyle);
            }
            for (int j = 1; j < cp; j++) {
                BigDecimal hj = new BigDecimal("0");
                for (int i = 2; i < rowP; i++) {
                    HSSFRow row1 = sheet3.getRow(i);
                    if(!ObjectUtils.isEmpty(row1.getCell(j))) {
                        hj = hj.add(new BigDecimal(row1.getCell(j).getStringCellValue()));
                    }
                }
                HSSFCell cell1 = rowHJ.createCell(j);
                cell1.setCellValue(hj.toString());
                cell1.setCellStyle(jeStyle);
            }
            HSSFCell cell1 = rowHJ.createCell(cp);
            cell1.setCellValue(totalSum.toString());
            cell1.setCellStyle(jeStyle);
            getCellSetBorder(workbook);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /*
     * 列头单元格样式
     */
    public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();

        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;

    }

    /*
     * 列数据信息单元格样式
     */
    public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //字体加粗
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public static HSSFCellStyle getJEStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //字体加粗
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("¥#,##0"));
        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public static HSSFCellStyle getPStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //字体加粗
        //font.setBold(true);
        //设置字体名字
        font.setFontName("Courier New");
        font.setFontHeightInPoints((short)8);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();

        style.setFont(font);
        style.setWrapText(true);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
    /*
        设置日期类别样式
     */
    public static HSSFCellStyle setDateTybeStyle(HSSFWorkbook workbook, String dateType){
        //设置字体
        HSSFFont font = workbook.createFont();
        //设置样式
        HSSFCellStyle style = workbook.createCellStyle();
        /*if("周末".equals(dateType)){
            //取色板
            HSSFPalette palette = workbook.getCustomPalette();
            //设置字体颜色
            font.setColor(HSSFColor.RED.index);
            //设置背景颜色
            //设置前景色  若不设置前景色，背景颜色出不来
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(palette.findSimilarColor(255,199,206).getIndex());
        }*/
        style.setFont(font);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    //循环遍历获取每一个cell，并设置样式
    public static void getCellSetBorder(HSSFWorkbook workbook){
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置字体
        HSSFFont font = workbook.createFont();
        //字体加粗
        //设置字体名字
        font.setFontName("Courier New");
        for(int i = 0;i < workbook.getNumberOfSheets();i++){
            //获取所有的表格
            HSSFSheet sheet = workbook.getSheetAt(i);
            isMergeRegion(sheet);
            for(int j = 0;j < sheet.getPhysicalNumberOfRows();j++){
                //获取所有的行
                if(j != 0){
                    HSSFRow row = sheet.getRow(j);
                    for(int k = 0;k < row.getPhysicalNumberOfCells();k++){
                        //获取所有的列
                        HSSFCell cell = row.getCell(k);
                        //当第二个表  第四个表格时   设置字体
                        if((i == 1 || i == 3) && j >= 2){
                            if(!ObjectUtils.isEmpty(cell)){
                                cellStyle.cloneStyleFrom(cell.getCellStyle());
                                cellStyle.setBorderBottom(BorderStyle.THIN);
                                cellStyle.setBorderLeft(BorderStyle.THIN);
                                cellStyle.setBorderRight(BorderStyle.THIN);
                                cellStyle.setBorderTop(BorderStyle.THIN);
                                //设置样式
                                cellStyle.setFont(font);
                                //设置自动换行
                                cellStyle.setWrapText(false);
                                //设置水平对齐的样式为居中对齐
                                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                                //设置垂直对齐的样式为居中对齐
                                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                                cell.setCellStyle(cellStyle);
                            }else{
                                cell = row.createCell(k);
                                cell.setCellStyle(setCellBorder(cellStyle));
                            }
                        }else{
                            if((i == 0 && j >= 1 && j <= 4) || (i == 2 && j == 1 && k == 0)){
                                cellStyle = workbook.createCellStyle();
                            }
                            if(!ObjectUtils.isEmpty(cell)){
                                cellStyle.cloneStyleFrom(cell.getCellStyle());
                                cellStyle.setBorderBottom(BorderStyle.THIN);
                                cellStyle.setBorderLeft(BorderStyle.THIN);
                                cellStyle.setBorderRight(BorderStyle.THIN);
                                cellStyle.setBorderTop(BorderStyle.THIN);
                                cell.setCellStyle(cellStyle);
                            }else{
                                cell = row.createCell(k);
                                cell.setCellStyle(setCellBorder(cellStyle));
                            }
                        }
                        //将当前样式清空
//                        cellStyle.setAlignment(HorizontalAlignment.LEFT);
                    }
                }
            }
        }

    }

    //设置边框颜色
    public static HSSFCellStyle setCellBorder(HSSFCellStyle cellStyle){
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        /*style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);*/
        return cellStyle;
    }

    //给合并单元格设置样式
    public static void isMergeRegion(HSSFSheet sheet){
        for(int i = 0 ;i < sheet.getNumMergedRegions();i++){
            CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
            RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN,cellRangeAddress,sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
        }
    }


    // draw cell line
    private static void drawLine(HSSFSheet sheet, HSSFRow row, int i, int j, int width, int height, int[] xys) {
        int cellWidth = (int) (PERCENT_WIDTH * PXTOPT * width);
        short cellHeight = (short) (PERCENT_HEIGHT * PXTOPT * height);
        sheet.setColumnWidth(j, cellWidth);
        row.setHeight(cellHeight);
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short) j, i, (short) (j), i);
        HSSFShapeGroup group = patriarch.createGroup(a);
        float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet);
        EscherGraphics g = new EscherGraphics(group, sheet.getWorkbook(), Color.black, verticalPointsPerPixel);
        EscherGraphics2d g2d = new EscherGraphics2d(g);
        for (int l = 0; l < xys.length; l += 2) {
            int x = (int) ((PERCENT_WIDTH * 0.75 * xys[l] / cellWidth) * 1023);
            int y = (int) ((PERCENT_HEIGHT * 0.75 * xys[l + 1] / cellHeight) * 255);
            g2d.drawLine(0, 0, x, y);
        }
    }

    public static void main(String[] args) {
        BigDecimal bg = new BigDecimal("0");
        bg = bg.add(new BigDecimal("30"));
        System.out.println(bg.toString());
    }

}